using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
	public class SettingData
	{
		#region Fields
		protected static SqlConnection dbConn = new SqlConnection(Config.Global.Settings.ConnectionString);
		#endregion

		#region Get Object Method
		private static object getNullObject(object obj)
		{
			return obj == null ? DBNull.Value : obj;
		}
		private static object getNullValue(object obj)
		{
			return obj == DBNull.Value ? null : obj;
		}
		#endregion

		#region Constructors
		public SettingData() {}
		#endregion

		#region Methods

		/// <summary>
		/// Saves a record to the Setting table.
		/// </summary>
		/// <param name="settingInfo"></param>
		/// <returns>ID of Saved record</returns>
		public static int? Insert(SettingInfo settingInfo)
		{
			SqlCommand dbCmd = new SqlCommand("[uspSettingInsert]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@SiteName", getNullObject(settingInfo.SiteName));
			dbCmd.Parameters.AddWithValue("@SiteLogo", getNullObject(settingInfo.SiteLogo));
			dbCmd.Parameters.AddWithValue("@SiteURL", getNullObject(settingInfo.SiteURL));
			dbCmd.Parameters.AddWithValue("@SiteEmail", getNullObject(settingInfo.SiteEmail));
			dbCmd.Parameters.AddWithValue("@SiteAddress", getNullObject(settingInfo.SiteAddress));
			dbCmd.Parameters.AddWithValue("@SitePhone", getNullObject(settingInfo.SitePhone));
			dbCmd.Parameters.AddWithValue("@SiteDescription", getNullObject(settingInfo.SiteDescription));
			dbCmd.Parameters.AddWithValue("@SiteKeyword", getNullObject(settingInfo.SiteKeyword));
			dbCmd.Parameters.AddWithValue("@SiteAbout", getNullObject(settingInfo.SiteAbout));
			dbCmd.Parameters.AddWithValue("@SiteYear", getNullObject(settingInfo.SiteYear));
			dbCmd.Parameters.AddWithValue("@SiteStatus", getNullObject(settingInfo.SiteStatus));
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value);
			}
			catch (Exception)
			{
				return null;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Updates a record in the Setting table.
		/// </summary>
		/// <param name="settingInfo"></param>
		/// <returns>true: successful; false: unsuccessful</returns>
		public static bool Update(SettingInfo settingInfo)
		{
			SqlCommand dbCmd = new SqlCommand("[uspSettingUpdate]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@SiteID", getNullObject(settingInfo.SiteID));
			dbCmd.Parameters.AddWithValue("@SiteName", getNullObject(settingInfo.SiteName));
			dbCmd.Parameters.AddWithValue("@SiteLogo", getNullObject(settingInfo.SiteLogo));
			dbCmd.Parameters.AddWithValue("@SiteURL", getNullObject(settingInfo.SiteURL));
			dbCmd.Parameters.AddWithValue("@SiteEmail", getNullObject(settingInfo.SiteEmail));
			dbCmd.Parameters.AddWithValue("@SiteAddress", getNullObject(settingInfo.SiteAddress));
			dbCmd.Parameters.AddWithValue("@SitePhone", getNullObject(settingInfo.SitePhone));
			dbCmd.Parameters.AddWithValue("@SiteDescription", getNullObject(settingInfo.SiteDescription));
			dbCmd.Parameters.AddWithValue("@SiteKeyword", getNullObject(settingInfo.SiteKeyword));
			dbCmd.Parameters.AddWithValue("@SiteAbout", getNullObject(settingInfo.SiteAbout));
			dbCmd.Parameters.AddWithValue("@SiteYear", getNullObject(settingInfo.SiteYear));
			dbCmd.Parameters.AddWithValue("@SiteStatus", getNullObject(settingInfo.SiteStatus));
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value)>0;
			}
			catch (Exception)
			{
				return false;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Deletes a record from the Setting table by its primary key.
		/// </summary>
		/// <param name="siteID"></param>
		/// <returns>true: successful; false: unsuccessful</returns>
		public static bool DeleteBySiteID(int siteID)
		{
			SqlCommand dbCmd = new SqlCommand("[uspSettingDeleteBySiteID]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;

			dbCmd.Parameters.AddWithValue("@SiteID", siteID);
			dbCmd.Parameters.AddWithValue("@RETURN_VALUE", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;

			try
			{
				dbConn.Open();
				dbCmd.ExecuteNonQuery();
				return Convert.ToInt32(dbCmd.Parameters["@RETURN_VALUE"].Value)>0;
			}
			catch (Exception)
			{
				return false;
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Selects a single record from the Setting table.
		/// </summary>
		/// <param name="siteID"></param>
		/// <returns>SettingInfo: A record from the Setting table by its primary key.</returns>
		public static SettingInfo SelectBySiteID(int siteID)
		{
			SqlCommand dbCmd = new SqlCommand("[uspSettingSelectBySiteID]", dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			SettingInfo settingInfo =  new SettingInfo();

			dbCmd.Parameters.AddWithValue("@SiteID", siteID);

			try
			{
				dbConn.Open();
				SqlDataReader dataReader = dbCmd.ExecuteReader();

				if (dataReader.Read())
				{
					settingInfo.SiteID = System.Convert.ToInt32(getNullValue(dataReader["SiteID"]));
					settingInfo.SiteName = System.Convert.ToString(getNullValue(dataReader["SiteName"]));
					settingInfo.SiteLogo = System.Convert.ToString(getNullValue(dataReader["SiteLogo"]));
					settingInfo.SiteURL = System.Convert.ToString(getNullValue(dataReader["SiteURL"]));
					settingInfo.SiteEmail = System.Convert.ToString(getNullValue(dataReader["SiteEmail"]));
					settingInfo.SiteAddress = System.Convert.ToString(getNullValue(dataReader["SiteAddress"]));
					settingInfo.SitePhone = System.Convert.ToString(getNullValue(dataReader["SitePhone"]));
					settingInfo.SiteDescription = System.Convert.ToString(getNullValue(dataReader["SiteDescription"]));
					settingInfo.SiteKeyword = System.Convert.ToString(getNullValue(dataReader["SiteKeyword"]));
					settingInfo.SiteAbout = System.Convert.ToString(getNullValue(dataReader["SiteAbout"]));
					settingInfo.SiteYear = System.Convert.ToString(getNullValue(dataReader["SiteYear"]));
					settingInfo.SiteStatus = System.Convert.ToBoolean(getNullValue(dataReader["SiteStatus"]));
				}

				dataReader.Close();
				return settingInfo;
			}
			catch (Exception)
			{
				return new SettingInfo();
			}
			finally
			{
				dbConn.Close();
			}
		}

		/// <summary>
		/// Selects all records from the Setting table.
		/// </summary>
		/// <return>DataTable</return>
		public static DataTable SelectAll()
		{
			SqlCommand dbCmd = new SqlCommand("[uspSettingSelectAll]",dbConn);
			dbCmd.CommandType = CommandType.StoredProcedure;
			DataTable dtbSetting =  new DataTable();

			try
			{
				dbConn.Open();
				SqlDataReader dataReader = dbCmd.ExecuteReader();

				if (dataReader.HasRows)
				{
					dtbSetting.Load(dataReader);
				}

				dataReader.Close();
				return dtbSetting;
			}
			catch (Exception)
			{
				return null;
			}
			finally
			{
				dbConn.Close();
			}
		}

		#endregion
	}
}
